package cn.ecnu.auction.dao;


import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.concurrent.TimeUnit;

import org.json.JSONObject;

import org.apache.log4j.Logger;
import org.json.JSONArray;

import ToolPackage.Json;

import cn.ecnu.auction.db.DbConnection;
import cn.ecnu.auction.po.Bid;

public class BidDAO {
	
private static Logger logger = Logger.getLogger(GoodsDAO.class);
	
	/**
	 * 竞拍出价
	 * @param gid 拍卖品id
	 * @param bidPrice 竞拍价
	 * @param uid 拍卖品提供者
	 * @result result=1 竞拍成功 result = 0 出价过低  result = -1 错误
	 */
	public int addPurchase(int gid, double bidPrice, int uid){
		Connection conn = DbConnection.getConnection();
		int result=1;
		int state = getResult(gid);
		double currentPrice=getCurrentPrice(gid);
		if(bidPrice <= currentPrice)
			result=0;
		if(state==1) //已结束
			result=-1;
		
		String sql = "insert into bid(uid, gid, bidPrice, result) values(?, ?, ?, ?)";
		
		try {
			PreparedStatement pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, uid);
			pstmt.setInt(2, gid);
			pstmt.setDouble(3, bidPrice);
			pstmt.setInt(4, state);
			
			logger.info(pstmt);
			pstmt.executeUpdate();
		} catch (Exception e) {
			e.printStackTrace();
			result = -1;
		} finally {
			DbConnection.closeConnection(conn);
		}
		
		//*************************************************
		if(result ==1)
		{//把当前竞价插入goods表 bidprice
			//并把当前竞价人插入goods表winnerid
			Connection conn1 = DbConnection.getConnection();
			String sql1 = "update Goods set currentPrice = " + bidPrice + " where gid = " + gid ;
			String sql2 = "update Goods set winnerid = "+uid +" where gid = "+gid;
			try {
				PreparedStatement pstmt=conn1.prepareStatement(sql1);
				pstmt.executeUpdate();
				pstmt.executeUpdate(sql2);
			}catch(Exception e){
				e.printStackTrace();
				result=-1;
			}finally{
				DbConnection.closeConnection(conn1);

			}
		}
		//**************************************************
		return result;
	}
	
	/**
	 * 查询最高拍卖价
	 * @param gid 拍卖品id
	 * @return currentPrice 现在价格
	 */
	private double getCurrentPrice(int gid){
		
		double price=0;
		Connection conn = DbConnection.getConnection();
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		String sql = "select currentPrice from goods where gid like '%" + gid + "%'";
		try {
			pstmt=conn.prepareStatement(sql);
			logger.info(pstmt);
			rs = pstmt.executeQuery();
			if (rs.next()) {
				price = rs.getDouble("currentPrice");
			}
		} catch (Exception e) {
			e.printStackTrace();
			logger.error(e);
		} finally {
			DbConnection.closeConnection(conn);
		}
		return price;
	}
	
	private int getResult(int gid){
		
		int result=0;
		Connection conn = DbConnection.getConnection();
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		String sql = "select result from goods where gid like '%" + gid + "%'";
		try {
			pstmt=conn.prepareStatement(sql);
			logger.info(pstmt);
			rs = pstmt.executeQuery();
			if (rs.next()) {
				result = rs.getInt("result");
			}
		} catch (Exception e) {
			e.printStackTrace();
			logger.error(e);
		} finally {
			DbConnection.closeConnection(conn);
		}
		return result;
	}
	
	

	public JSONArray getPurchasing(int uid)
	{
		Connection conn = DbConnection.getConnection();
		PreparedStatement pstmt = null;
		PreparedStatement pstmt2 = null;
		ResultSet rs = null;
		String sql1 = "SELECT gid,MAX(bidPrice) FROM bid WHERE result=0 and uid="+uid +" GROUP BY gid";
		
		JSONArray jsonArray = new JSONArray();
		JSONObject json = null;
		
		try{
			pstmt=conn.prepareStatement(sql1);
			rs = pstmt.executeQuery();
			while(rs.next()){
				json = new JSONObject();
				json.put("gid", rs.getInt("gid"));
				json.put("bidPrice", rs.getDouble("MAX(bidPrice)"));
				
				String sql2 = "select * from Goods where gid = " + rs.getInt("gid");
				pstmt2=conn.prepareStatement(sql2);
				ResultSet rs2 = pstmt2.executeQuery();
				if(rs2.next())
				{
					json.put("gname", rs2.getString("gname"));
					json.put("currentPrice", rs2.getDouble("currentPrice"));
					json.put("type",rs2.getString("type"));
					
					//about time
					Date currentDate = new Date();
	
					SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
					String startDateStr =rs2.getString("startTime");
					
					Date startDate = sdf.parse(startDateStr);
					double lasting = rs2.getDouble("lastingTime");
					Calendar c = Calendar.getInstance();
					c.setTime(startDate);
					c.add(Calendar.MINUTE, (int) (lasting*60));
					
					Date endDate=c.getTime();
					
					long millis=(endDate.getTime()-currentDate.getTime());
					System.out.println("~~~~~~~~~"+ currentDate.toString()+"  " +millis);
					int hour= (int)TimeUnit.MILLISECONDS.toHours(millis);
					millis=millis-(int)TimeUnit.MILLISECONDS.toHours(millis)*60*60*1000;	
					int minute= (int)TimeUnit.MILLISECONDS.toMinutes(millis);
					millis=millis-(int)TimeUnit.MILLISECONDS.toMinutes(millis)*60*1000;
					int second= (int)TimeUnit.MILLISECONDS.toSeconds(millis);
					
					json.put("leftHours", hour);
					json.put("leftMins",minute);
					json.put("leftSecs", second);
				
				}
				jsonArray.put(json);
			}

		}
		catch (Exception e) {
			e.printStackTrace();
			logger.error(e);
	    } 
		finally {
			DbConnection.closeConnection(conn);
		}	
		return jsonArray;
	}
	 
	public JSONArray getPurchased(int uid)
	{
		Connection conn = DbConnection.getConnection();
		PreparedStatement pstmt = null;
		PreparedStatement pstmt2 = null;
		ResultSet rs = null;
		String sql1 = "select gid,MAX(bidPrice) from bid where result=1 and uid="+uid +" group by gid";
		
		JSONArray jsonArray = new JSONArray();
		JSONObject json = null;
		
		try{
			pstmt=conn.prepareStatement(sql1);
			rs = pstmt.executeQuery();
			while(rs.next()){
				json = new JSONObject();
				json.put("bidPrice", rs.getDouble("MAX(bidPrice)"));
				
				String sql2 = "select * from Goods where gid =" + rs.getInt("gid");
				pstmt2=conn.prepareStatement(sql2);
				ResultSet rs2 = pstmt2.executeQuery();
				if(rs2.next())
				{
					json.put("gname", rs2.getString("gname"));
					json.put("currentPrice", rs2.getDouble("currentPrice"));
					json.put("type",rs2.getString("type"));
				}
							
				jsonArray.put(json);
			}

		}
		catch (Exception e) {
			e.printStackTrace();
			logger.error(e);
	    } 
		finally {
			DbConnection.closeConnection(conn);
		}	
		return jsonArray;
	}

	public JSONArray getMySelling(int uid)
	{
		Connection conn = DbConnection.getConnection();
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		String sql = "select * from Goods where ownerid="+uid+" and result=0" ;
		
		JSONArray jsonArray = new JSONArray();
		JSONObject json = null;
		
		try{		
			pstmt=conn.prepareStatement(sql);
			rs = pstmt.executeQuery();
			while(rs.next()){
				json = new JSONObject();
				json.put("gname", rs.getString("gname"));
				json.put("currentPrice", rs.getDouble("currentPrice"));
				json.put("startPrice", rs.getDouble("startPrice"));
				json.put("type", rs.getString("type"));
			    
				//****about time **********************************
				Date currentDate = new Date();
				
				SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
				String startDateStr =rs.getString("startTime");
				
				Date startDate = sdf.parse(startDateStr);
				double lasting = rs.getDouble("lastingTime");
				Calendar c = Calendar.getInstance();
				c.setTime(startDate);
				c.add(Calendar.MINUTE, (int) (lasting*60));
				
				Date endDate=c.getTime();
				
				long millis=(endDate.getTime()-currentDate.getTime());
				System.out.println("~~~~~~~~~"+ currentDate.toString()+"  " +millis);
				int hour= (int)TimeUnit.MILLISECONDS.toHours(millis);
				millis=millis-(int)TimeUnit.MILLISECONDS.toHours(millis)*60*60*1000;	
				int minute= (int)TimeUnit.MILLISECONDS.toMinutes(millis);
				millis=millis-(int)TimeUnit.MILLISECONDS.toMinutes(millis)*60*1000;
				int second= (int)TimeUnit.MILLISECONDS.toSeconds(millis);
				
				json.put("leftHours", hour);
				json.put("leftMins",minute);
				json.put("leftSecs", second);
				//*************************************************
				jsonArray.put(json);
			}

		}
		catch (Exception e) {
			e.printStackTrace();
			logger.error(e);
	    } 
		finally {
			DbConnection.closeConnection(conn);
		}	
		return jsonArray;
	}

	public JSONArray getMySelled(int uid)
	{
		Connection conn = DbConnection.getConnection();
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		String sql = "select * from Goods where ownerid="+uid+" and result=1" ;
		
		JSONArray jsonArray = new JSONArray();
		JSONObject json = null;
		
		try{	
			pstmt=conn.prepareStatement(sql);
			rs = pstmt.executeQuery();
			while(rs.next()){
				json = new JSONObject();
				json.put("gname", rs.getString("gname"));
				json.put("currentPrice", rs.getDouble("currentPrice"));
				json.put("type", rs.getString("type"));
							
				jsonArray.put(json);
			}
		}
		catch (Exception e) {
			e.printStackTrace();
			logger.error(e);
	    } 
		finally {
			DbConnection.closeConnection(conn);
		}	
		return jsonArray;
	}

	public JSONArray getPurchasingById(int uid, int gid)
	{
		Connection conn = DbConnection.getConnection();
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		
		JSONArray jsonArray = new JSONArray();
		JSONObject json = null;
		
		String sql1 = "select MAX(bidPrice) from bid where uid="+uid+" and gid="+gid;
		try{
			json = new JSONObject();
			pstmt=conn.prepareStatement(sql1);
			rs = pstmt.executeQuery();
			
			if(rs.next()){
				json.put("bidPrice", rs.getDouble("MAX(bidPrice)"));
				
				String sql2 = "select * from Goods where gid="+gid;
				ResultSet rs2 = pstmt.executeQuery(sql2);
				rs2.next();
				json.put("gname", rs2.getString("gname"));
				json.put("description", rs2.getString("description"));
				json.put("currentPrice", rs2.getDouble("currentPrice"));
				json.put("type", rs2.getString("type"));
				
				//*************************************************
				//about time
				Date currentDate = new Date();
				SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
				String startDateStr =rs2.getString("startTime");
				
				Date startDate = sdf.parse(startDateStr);
				double lasting = rs2.getDouble("lastingTime");
				Calendar c = Calendar.getInstance();
				c.setTime(startDate);
				c.add(Calendar.MINUTE, (int) (lasting*60));
				
				Date endDate=c.getTime();
				
				long millis=(endDate.getTime()-currentDate.getTime());
				int hour= (int)TimeUnit.MILLISECONDS.toHours(millis);
				millis=millis-(int)TimeUnit.MILLISECONDS.toHours(millis)*60*60*1000;
				int minute= (int)TimeUnit.MILLISECONDS.toMinutes(millis);
				millis=millis-(int)TimeUnit.MILLISECONDS.toMinutes(millis)*60*1000;
				int second= (int)TimeUnit.MILLISECONDS.toSeconds(millis);
				json.put("leftHours", hour);
				json.put("leftMins",minute);
				json.put("leftSecs", second);
				//************************************************
				
				String sql3 = "select * from User where userId="+rs2.getInt("ownerid");
				ResultSet rs3 = pstmt.executeQuery(sql3);
				rs3.next();
				json.put("owername", rs3.getString("userName"));
			}
			jsonArray.put(json);
		}catch (Exception e) {
			e.printStackTrace();
			logger.error(e);
	    } 
		finally {
			DbConnection.closeConnection(conn);
		}	
		return jsonArray;		
	}
	
}
